package com.lmonkey.servic;

import com.lmonkey.dao.Basedao;
import com.lmonkey.shop.PRODUCT;

import javax.servlet.jsp.jstl.sql.Result;
import javax.swing.table.TableRowSorter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class PRODUCTDao {
    public static ArrayList<PRODUCT> selectAll(int cpage,int count,String keyword){
        ArrayList<PRODUCT> list = new ArrayList<PRODUCT>();
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try{
            String sql = null;
            if (keyword != null){
                sql = "select * from lmonkey_product where product_name like ? limit ?,?";
                ps =conn.prepareStatement(sql);
                ps.setString(1,"%"+keyword+"%");
                ps.setInt(2,(cpage-1)*count);//从哪开始查
                ps.setInt(3,count);//查多少个
            }else{
                sql = "select * from lmonkey_product limit ?,?";
                ps =conn.prepareStatement(sql);
                ps.setInt(1,(cpage-1)*count);//从哪开始查
                ps.setInt(2,count);//查多少个
            }

            rs = ps.executeQuery();
            while (rs.next()){
                PRODUCT p = new PRODUCT(
                      rs.getInt("product_id"),
                      rs.getString("product_name"),
                      rs.getString("product_description"),
                      rs.getInt("product_price"),
                      rs.getInt("product_stock"),
                      rs.getInt("product_fid"),
                      rs.getInt("product_cid"),
                      rs.getString("product_filename")
                );
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public static int[] totalpage(int count,String keyword){
        //记录总记录数0和1总页数，
        int arr[] = {0,1};
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            String sql = null;
            if (keyword!=null){
                sql = "select count(*) from lmonkey_product where product_name like ?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,"%"+keyword+"%");
            }else{
                sql = "select count(*) from lmonkey_product";
                ps = conn.prepareStatement(sql);
            }

            rs = ps.executeQuery();
            while (rs.next()){
                arr[0] = rs.getInt(1);
            }
            if (arr[0]%count==0){
                arr[1] = arr[0]/count;
            }else if (arr[0]%count != 0){
                arr[1] = (arr[0]/count)+1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arr;
    }
    public static PRODUCT selectByid(int id){
        PRODUCT p = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            String sql = "select * from lmonkey_product where product_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                p =new PRODUCT(
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getString("product_description"),
                        rs.getInt("product_price"),
                        rs.getInt("product_stock"),
                        rs.getInt("product_fid"),
                        rs.getInt("product_cid"),
                        rs.getString("product_filename")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }

    public static ArrayList<PRODUCT> selectByfid(int id){
        ArrayList<PRODUCT> list = new ArrayList<PRODUCT>();
        //设置结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "select * from lmonkey_product where product_fid = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                    PRODUCT p = new PRODUCT(
                            rs.getInt("product_id"),
                            rs.getString("product_name"),
                            rs.getString("product_description"),
                            rs.getInt("product_price"),
                            rs.getInt("product_stock"),
                            rs.getInt("product_fid"),
                            rs.getInt("product_cid"),
                            rs.getString("product_filename")
                    );
                    list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static ArrayList<PRODUCT> selectBycid(int cid){
        ArrayList<PRODUCT> list1 = new ArrayList<PRODUCT>();
        //设置结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "select * from lmonkey_product where product_cid = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,cid);
            rs = ps.executeQuery();
            while(rs.next()){
                PRODUCT p1 = new PRODUCT(
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getString("product_description"),
                        rs.getInt("product_price"),
                        rs.getInt("product_stock"),
                        rs.getInt("product_fid"),
                        rs.getInt("product_cid"),
                        rs.getString("product_filename")
                );
                list1.add(p1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list1;
    }

    public static ArrayList<PRODUCT> selectByid1(ArrayList<Integer> id){
        ArrayList<PRODUCT> lastlist = new ArrayList<PRODUCT>();
        PRODUCT p = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            for (int i=0;i<id.size();i++){
                String sql = "select * from lmonkey_product where product_id = ?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1,id.get(i));
                rs = ps.executeQuery();
                while (rs.next()){
                    p =new PRODUCT(
                            rs.getInt("product_id"),
                            rs.getString("product_name"),
                            rs.getString("product_description"),
                            rs.getInt("product_price"),
                            rs.getInt("product_stock"),
                            rs.getInt("product_fid"),
                            rs.getInt("product_cid"),
                            rs.getString("product_filename")
                    );
                    lastlist.add(p);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lastlist;
    }

    public  static ArrayList<PRODUCT> selectBykeyword(String keyword){
        ArrayList<PRODUCT> p = new ArrayList<PRODUCT>();
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置结果集
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_product where product_name like ? or product_description like ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"%"+keyword+"%");
            ps.setObject(2,"%"+keyword+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                PRODUCT p1 =new PRODUCT(
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getString("product_description"),
                        rs.getInt("product_price"),
                        rs.getInt("product_stock"),
                        rs.getInt("product_fid"),
                        rs.getInt("product_cid"),
                        rs.getString("product_filename")
                );
                p.add(p1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return p;
    }

    public static PRODUCT selectByname(String name){
        PRODUCT p = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            String sql = "select * from lmonkey_product where product_filename = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            rs = ps.executeQuery();
            while (rs.next()){
                p =new PRODUCT(
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getString("product_description"),
                        rs.getInt("product_price"),
                        rs.getInt("product_stock"),
                        rs.getInt("product_fid"),
                        rs.getInt("product_cid"),
                        rs.getString("product_filename")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }
}
